/*
 * UserDaoImpl.java
 *
 * Copyright 2012 xwwx, Inc. All rights reserved.
 * 
 */
package com.xwwx.dao.impl;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.ArrayList;
import java.util.List;

import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.support.JdbcDaoSupport;

import com.xwwx.common.PageList;
import com.xwwx.common.Pages;
import com.xwwx.dao.UserDao;
import com.xwwx.exception.XwwxException;
import com.xwwx.pojo.User;

/*
 * @author  xwwx
 * @version 1.0, 2012-11-13
 * @since   JDK1.6
 * 
 * 用户数据库操作实现类
 */
public class UserDaoImpl extends JdbcDaoSupport implements UserDao {

	/** 根据用户名密码返回用户对象 **/
	@Override
	public User getUserByIdAndPwd(String userid, String password){

		String sql = "select userid,username from usertbl where userid=? and password=?";
		List list = null;
		try {
			RowMapper mapper = new RowMapper(){
				@Override
				public Object mapRow(ResultSet arg0, int arg1)
						throws SQLException {

					User user = new User();
					user.setUserid(arg0.getString("userid"));
					user.setUsername(arg0.getString("username"));
					return user;
				}
			};
			list = this.getJdbcTemplate().query(sql,new Object[] { userid, password}, mapper);
			return (list == null || list.isEmpty())?null:(User)list.get(0);
		}catch (EmptyResultDataAccessException e) {
			return null;
		}
	}

	/** 得到全部用户 **/
	@SuppressWarnings("unchecked")
	public List<User> getUserList(String orderby, String ascordesc,
			int pagesize, int page,String wherestr){
		
		String sql = "select top "+pagesize
						+" userid,username from usertbl " +
						"where "+wherestr+" and (userid not in(select top "+((page-1)*pagesize)+" userid from usertbl where "+wherestr+" order by " + orderby + " " + ascordesc +")) order by " + orderby + " " + ascordesc;
		System.out.println(sql);
		try {
			
			RowMapper mapper = new RowMapper(){

				@Override
				public Object mapRow(ResultSet arg0, int arg1)
						throws SQLException {

					User user = new User();
					user.setUserid(arg0.getString("userid"));
					user.setUsername(arg0.getString("username"));
					
					return user;
				}
				
			};
			
			return (List<User>)this.getJdbcTemplate().query(sql, mapper);

		}catch (EmptyResultDataAccessException e) {
			
			return null;
		}
	}
	
	/** 得到所有用户数量 **/
	public long getAllUserNum(String wherestr){
		
		StringBuffer sb = new StringBuffer(); 
		String sql = sb.append("select count(USERID) from usertbl")
						.append(" where ")
						.append(wherestr).toString();
		sb = null;
		return this.getJdbcTemplate().queryForLong(sql);
	}
	
	/** 分页得到用户列表  **/
	public  PageList getUserListFy(String orderby, String ascordesc,
			int pagesize, int page,String wherestr) throws XwwxException{
		
		Connection conn = null;
		CallableStatement callStmt = null;
		ResultSet rs = null;
		List<User> list = null;

		try{
			conn = this.getJdbcTemplate().getDataSource().getConnection();
			callStmt = conn.prepareCall("{call sp_PageView(?,?,?,?,?,?,?,?)}");
	        // 参数index从1开始，依次 1,2,3...
			callStmt.setString(1, "usertbl");
			callStmt.setString(2, "userid");
			callStmt.setInt(3, page);
			callStmt.setInt(4, pagesize);
			callStmt.setString(5, "");
			callStmt.setString(6, "");
			callStmt.setString(7, "1=1");
			callStmt.registerOutParameter(8,Types.INTEGER);
			int outresult = 0;
	        rs = callStmt.executeQuery();
	        list = new ArrayList<User>();
	        while(rs.next()){
	        	User user = new User();
	        	user.setUserid(rs.getString("userid"));
	        	user.setUsername(rs.getString("username"));
	        	list.add(user);
	        }
	        if (!callStmt.getMoreResults()) {
		        outresult = callStmt.getInt(8);
	        }
			PageList pl = new PageList();
			Pages pages = new Pages();
			pages.setAllPage(outresult);
			pages.setPage(page);
			pages.setPerPageNum(pagesize);
			pl.setObjectList(list);
			pl.setPages(pages);
			
	        return pl;
		}catch(Exception e){
			e.printStackTrace();
			throw (XwwxException)e;
		}finally{
			try{
				if(rs != null){
					rs.close();
				}
				if(callStmt != null){
					callStmt.close();
				}
				if(conn != null){
					conn.close();
				}
			}catch(Exception e){
				e.printStackTrace();
			}
		}
	}
}
